﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace WCSY.DAL
{
    public class fg_approveObjDetAppService
    {
        public fg_approveObjDetAppService()
		{}
		#region  Method

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return DBHelper.GetMaxID("lineid", "fg_approveObjDetApp"); 
		}


		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(string id,int lineid)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from fg_approveObjDetApp");
			strSql.Append(" where id='"+id+"' and lineid="+lineid+" ");
			return DBHelper.Exists(strSql.ToString());
		}

		/// <summary>
		/// 增加一条数据
		/// </summary>
		public string Add(WCSY.MODEL.fg_approveObjDetApp model)
		{
			StringBuilder strSql=new StringBuilder();
			StringBuilder strSql1=new StringBuilder();
			StringBuilder strSql2=new StringBuilder();
			if (model.id != null)
			{
				strSql1.Append("id,");
				strSql2.Append("'"+model.id+"',");
			}
			if (model.lineid != null)
			{
				strSql1.Append("lineid,");
				strSql2.Append(""+model.lineid+",");
			}
			if (model.approvPsn != null)
			{
				strSql1.Append("approvPsn,");
				strSql2.Append("'"+model.approvPsn+"',");
			}
			if (model.flowStepNo != null)
			{
				strSql1.Append("flowStepNo,");
				strSql2.Append(""+model.flowStepNo+",");
			}
			if (model.checkInfo != null)
			{
				strSql1.Append("checkInfo,");
				strSql2.Append("'"+model.checkInfo+"',");
			}
			if (model.checkFlg != null)
			{
				strSql1.Append("checkFlg,");
				strSql2.Append(""+model.checkFlg+",");
			}
			if (model.dealdate != null)
			{
				strSql1.Append("dealdate,");
				strSql2.Append("'"+model.dealdate+"',");
			}
			if (model.rejectDate != null)
			{
				strSql1.Append("rejectDate,");
				strSql2.Append("'"+model.rejectDate+"',");
			}
			if (model.remarks != null)
			{
				strSql1.Append("remarks,");
				strSql2.Append("'"+model.remarks+"',");
			}
			strSql.Append("insert into fg_approveObjDetApp(");
			strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
			strSql.Append(")");
			strSql.Append(" values (");
			strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
			strSql.Append(")");
			return strSql.ToString();
			
		}

		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(WCSY.MODEL.fg_approveObjDetApp model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update fg_approveObjDetApp set ");
			if (model.approvPsn != null)
			{
				strSql.Append("approvPsn='"+model.approvPsn+"',");
			}
			else
			{
				strSql.Append("approvPsn= null ,");
			}
			if (model.flowStepNo != null)
			{
				strSql.Append("flowStepNo="+model.flowStepNo+",");
			}
			else
			{
				strSql.Append("flowStepNo= null ,");
			}
			if (model.checkInfo != null)
			{
				strSql.Append("checkInfo='"+model.checkInfo+"',");
			}
			else
			{
				strSql.Append("checkInfo= null ,");
			}
			if (model.checkFlg != null)
			{
				strSql.Append("checkFlg="+model.checkFlg+",");
			}
			else
			{
				strSql.Append("checkFlg= null ,");
			}
			if (model.dealdate != null)
			{
				strSql.Append("dealdate='"+model.dealdate+"',");
			}
			else
			{
				strSql.Append("dealdate= null ,");
			}
			if (model.rejectDate != null)
			{
				strSql.Append("rejectDate='"+model.rejectDate+"',");
			}
			else
			{
				strSql.Append("rejectDate= null ,");
			}
			if (model.remarks != null)
			{
				strSql.Append("remarks='"+model.remarks+"',");
			}
			else
			{
				strSql.Append("remarks= null ,");
			}
			int n = strSql.ToString().LastIndexOf(",");
			strSql.Remove(n, 1);
			strSql.Append(" where id='"+ model.id+"' and lineid="+ model.lineid+" ");
			int rowsAffected=DBHelper.ExecuteSql(strSql.ToString());
			if (rowsAffected > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
        /// <summary>
        /// 更改单据审核步骤标志
        /// </summary>
        /// <param name="checkinfo">审批意见</param>
        /// <param name="checkFlag">审批标志</param>
        /// <param name="billno">单据代码</param>
        /// <param name="approvpsn">审批岗位</param>
        /// <returns></returns>
        public string updApprovState(string checkinfo,int checkFlag,string billno,string approvpsn) {

            string sql = "update fg_approveObjDetApp set checkinfo='"+checkinfo+"',checkflg="+checkFlag+",dealdate=getdate() from fg_approveObjMstApp "+
                        "where fg_approveObjMstApp.billno='"+billno+"' and fg_approveObjDetApp.id=fg_approveObjMstApp.id and approvpsn='"+approvpsn+"' ";
            return sql;
        }
        /// <summary>
        /// 驳回功能，删除后面所有审批步骤
        /// </summary>
        /// <param name="checkinfo"></param>
        /// <param name="checkFlag"></param>
        /// <param name="billno">单据编码</param>
        /// <param name="approvpsn">审批岗位</param>
        /// <returns></returns>
        public string delApprovState(string checkinfo, int checkFlag, string billno, string approvpsn)
        {
            string sql = @"declare @flowstep int,
                        @id varchar(255)
                        set @flowstep = (select flowstepno from fg_approveobjdetapp as objdet
                        left join fg_approveobjmstapp as objmst on objdet.id = objmst.id  
                        where objmst.billno='"+billno+"' and approvpsn='"+approvpsn+"' and checkflg=2)"+
                        @"set @id = (select objdet.id from fg_approveobjdetapp as objdet
                        left join fg_approveobjmstapp as objmst on objdet.id = objmst.id  
                        where objmst.billno='"+billno+"' and approvpsn='"+approvpsn+"' and checkflg=2)"+
                        @"delete fg_approveobjdetapp where id=@id and flowstepno>@flowstep";
            return sql;
        }
        /// <summary>
        /// 删除审批流步骤明细表（重新送审与取消审批使用）
        /// </summary>
        /// <param name="billno">单据号</param>
        /// <returns></returns>
        public string delApprovobjdetByBillno(string billno)
        {
            string sql = @"delete fg_approveobjdetapp from fg_approveobjmstapp
where fg_approveobjmstapp.id = fg_approveobjdetapp.id and fg_approveobjmstapp.billno='" + billno + "'";
            return sql;
        }

        /// <summary>
        /// 获取登录岗位未审批单据（待办事项用）
        /// </summary>
        /// <param name="approvpsn">审批人</param>
        /// <param name="sendpsn">发起人</param>
        /// <returns></returns>
        public DataTable getApprovPsnObj(string approvpsn,string sendpsn) {
            string sql = @"
--待审批
select operatesapp.opname,objmstapp.billno,objmstapp.sendpsn,mg_user.username as sendpsnname,Orderdet.money,objdetapp.flowstepno,'待审批' as status from 
(select id,min(flowstepno) as flowstepno from fg_approveObjDetApp  
			where (checkflg is null or checkflg =0)
			group by id) as objdetapp --审批流步骤
left join fg_approveObjMstApp as objmstapp on objdetapp.id = objmstapp.id --审批步骤主表
left join fg_operatesApp as operatesapp on objmstapp.opno = operatesapp.opno --单据控制表
left join (select sysno,sum(applymoney) as money from wcsy_costsOrderdet group by sysno) as Orderdet on objmstapp.billno = Orderdet.sysno --明细金额统计
left join fg_approveObjDetApp on fg_approveObjDetApp.id = objdetapp.id and fg_approveObjDetApp.flowstepno = objdetapp.flowstepno --审批流步骤明细表
left join mg_user on objmstapp.sendpsn = mg_user.userid 
where fg_approveObjDetApp.approvpsn = '"+approvpsn+"' "+ 
@"union all
--已驳回
select operatesapp.opname,objmstapp.billno,objmstapp.sendpsn,mg_user.username as sendpsnname,Orderdet.money,objdetapp.flowstepno,'已驳回' as status from 
(select id,min(flowstepno) as flowstepno from fg_approveObjDetApp  
			where checkflg =2
			group by id) as objdetapp --审批流步骤
left join fg_approveObjMstApp as objmstapp on objdetapp.id = objmstapp.id --审批步骤主表
left join fg_operatesApp as operatesapp on objmstapp.opno = operatesapp.opno --单据控制表
left join (select sysno,sum(applymoney) as money from wcsy_costsOrderdet group by sysno) as Orderdet on objmstapp.billno = Orderdet.sysno --明细金额统计
left join wcsy_costsordermst as ordermst on objmstapp.billno = ordermst.sysno -- 单据主表
left join mg_user on objmstapp.sendpsn = mg_user.userid 
where ordermst.authorpsn = '"+sendpsn+"'  ";
            return DBHelper.GetDataTable(sql);
        }
        /// <summary>
        /// 根据条件查询审批步骤明细表数据
        /// </summary>
        /// <param name="where">条件</param>
        /// <returns></returns>
        public DataTable getApprovobjDet(string where) {
            string sql = @"select * from fg_approveobjdetapp
                        left join fg_approveobjmstapp on fg_approveobjdetapp.id=fg_approveobjmstapp.id where 1=1 ";
            if(!where.Equals("")){
                sql += where;
            }

            return DBHelper.GetDataTable(sql);
        
        }
		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(string id,int lineid)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from fg_approveObjDetApp ");
			strSql.Append(" where id='"+id+"' and lineid="+lineid+" " );
			int rowsAffected=DBHelper.ExecuteSql(strSql.ToString());
			if (rowsAffected > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public WCSY.MODEL.fg_approveObjDetApp GetModel(string id,int lineid)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1  ");
			strSql.Append(" id,lineid,approvPsn,flowStepNo,checkInfo,checkFlg,dealdate,rejectDate,remarks ");
			strSql.Append(" from fg_approveObjDetApp ");
			strSql.Append(" where id='"+id+"' and lineid="+lineid+" " );
			WCSY.MODEL.fg_approveObjDetApp model=new WCSY.MODEL.fg_approveObjDetApp();
			DataSet ds=DBHelper.Query(strSql.ToString());
			if(ds.Tables[0].Rows.Count>0)
			{
				if(ds.Tables[0].Rows[0]["id"]!=null && ds.Tables[0].Rows[0]["id"].ToString()!="")
				{
					model.id=ds.Tables[0].Rows[0]["id"].ToString();
				}
				if(ds.Tables[0].Rows[0]["lineid"]!=null && ds.Tables[0].Rows[0]["lineid"].ToString()!="")
				{
					model.lineid=int.Parse(ds.Tables[0].Rows[0]["lineid"].ToString());
				}
				if(ds.Tables[0].Rows[0]["approvPsn"]!=null && ds.Tables[0].Rows[0]["approvPsn"].ToString()!="")
				{
					model.approvPsn=ds.Tables[0].Rows[0]["approvPsn"].ToString();
				}
				if(ds.Tables[0].Rows[0]["flowStepNo"]!=null && ds.Tables[0].Rows[0]["flowStepNo"].ToString()!="")
				{
					model.flowStepNo=int.Parse(ds.Tables[0].Rows[0]["flowStepNo"].ToString());
				}
				if(ds.Tables[0].Rows[0]["checkInfo"]!=null && ds.Tables[0].Rows[0]["checkInfo"].ToString()!="")
				{
					model.checkInfo=ds.Tables[0].Rows[0]["checkInfo"].ToString();
				}
				if(ds.Tables[0].Rows[0]["checkFlg"]!=null && ds.Tables[0].Rows[0]["checkFlg"].ToString()!="")
				{
					model.checkFlg=int.Parse(ds.Tables[0].Rows[0]["checkFlg"].ToString());
				}
				if(ds.Tables[0].Rows[0]["dealdate"]!=null && ds.Tables[0].Rows[0]["dealdate"].ToString()!="")
				{
					model.dealdate=DateTime.Parse(ds.Tables[0].Rows[0]["dealdate"].ToString());
				}
				if(ds.Tables[0].Rows[0]["rejectDate"]!=null && ds.Tables[0].Rows[0]["rejectDate"].ToString()!="")
				{
					model.rejectDate=DateTime.Parse(ds.Tables[0].Rows[0]["rejectDate"].ToString());
				}
				if(ds.Tables[0].Rows[0]["remarks"]!=null && ds.Tables[0].Rows[0]["remarks"].ToString()!="")
				{
					model.remarks=ds.Tables[0].Rows[0]["remarks"].ToString();
				}
				return model;
			}
			else
			{
				return null;
			}
		}
		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select id,lineid,approvPsn,flowStepNo,checkInfo,checkFlg,dealdate,rejectDate,remarks ");
			strSql.Append(" FROM fg_approveObjDetApp ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DBHelper.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" id,lineid,approvPsn,flowStepNo,checkInfo,checkFlg,dealdate,rejectDate,remarks ");
			strSql.Append(" FROM fg_approveObjDetApp ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DBHelper.Query(strSql.ToString());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM fg_approveObjDetApp ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = DBHelper.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.lineid desc");
			}
			strSql.Append(")AS Row, T.*  from fg_approveObjDetApp T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DBHelper.Query(strSql.ToString());
		}

		/*
		*/

		#endregion  Method
    }
}
